package com.ship.dispatch.service.decisionAnalysis.impl;

import cn.hutool.http.HttpUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.google.gson.Gson;
import com.google.gson.JsonObject;
import com.ship.common.core.domain.R;
import com.ship.common.core.exception.BusException;
import com.ship.common.core.web.domain.BaseResultCode;
import com.ship.common.core.web.domain.JsonResult;
import com.ship.common.business.service.impl.BaseServiceImpl;
import com.ship.common.core.utils.BeanCopyUtils;
import com.ship.common.core.utils.DateUtil;
import com.ship.dispatch.bean.boats.info.CheckRentDataInfo;
import com.ship.dispatch.bean.decisionAnalysis.SmShipRent;
import com.ship.dispatch.bean.fob.SpFobFileRecord;
import com.ship.dispatch.bean.sys.SysConstant;
import com.ship.dispatch.bo.SmShipRentAnalysisBo;
import com.ship.dispatch.bo.SmShipRentBrokerAddBo;
import com.ship.dispatch.bo.SmShipRentBrokerListBo;
import com.ship.dispatch.bo.SmShipRentBrokerSummaryListBo;
import com.ship.dispatch.bpm.utils.CalculateDateUtil;
import com.ship.dispatch.mapper.decisionAnalysis.SmShipRentMapper;
import com.ship.dispatch.model.Vo.SmShipRentAnalysisFormatVo;
import com.ship.dispatch.model.Vo.SmShipRentAnalysisVo;
import com.ship.dispatch.model.execl.ShipRentExcel;
import com.ship.dispatch.model.execl.ShipRentImportListener;
import com.ship.dispatch.service.decisionAnalysis.SmShipRentService;
import com.ship.dispatch.service.sys.SysConstantService;
import com.ship.dispatch.util.IDUtil;
import com.ship.dispatch.util.NumberFormatUtil;
import com.ship.common.security.utils.SecurityUtils;
import com.ship.dispatch.util.constant.Constants;
import com.ship.dispatch.util.constant.ShipRentConstants;
import com.ship.system.api.RemoteFileService;
import com.ship.system.api.domain.SysFile;
import org.apache.commons.collections4.map.HashedMap;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.List;
import java.util.stream.Collectors;

@Service
public class SmShipRentServiceImpl extends BaseServiceImpl<SmShipRentMapper, SmShipRent> implements SmShipRentService {

    @Autowired
    private SysConstantService constantService;

    @Value("${file_server.host}")
    private String fileServerHost;

    @Autowired
    private RemoteFileService remoteFileService;

    /**
     * 根据sheet导入租金表
     *
     * @param shipRentExcels 导入数据集合
     * @param sheetName      sheet名称
     */
    @Override
    public void importShipRent(List<ShipRentExcel> shipRentExcels, String sheetName) {
        if (null != shipRentExcels && shipRentExcels.size() > 0) {
            shipRentExcels.stream().forEach(s -> {
                List<SmShipRent> shipRents = this.list(new LambdaQueryWrapper<SmShipRent>()
                        .eq(SmShipRent::getRentWeek, s.getWeek())
                        .eq(SmShipRent::getRentDate, s.getDate())
                        .eq(SmShipRent::getBroker, sheetName)
                );
                if (shipRents.size() == 0) {
                    SmShipRent shipRent = new SmShipRent();
                    shipRent.setCreateBy(SecurityUtils.getStringUserId());
                    shipRent.setCreateDate(new Date());
                    shipRent.setRentWeek(s.getWeek());
                    shipRent.setRentDate(s.getDate());
                    shipRent.setBroker(sheetName);
                    shipRent.setUpdateBy(shipRent.getCreateBy());
                    shipRent.setUpdateDate(shipRent.getCreateDate());

                    for (int i = 0; i < 3; i++) {
                        shipRent.setId(IDUtil.nextId());
                        if (i == 0) {
                            shipRent.setVesselType(1);
                            if (StringUtils.isNotBlank(s.getWestSt())) {
                                shipRent.setSpotWest(new BigDecimal(s.getWestSt().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getEastSt())) {
                                shipRent.setSpotEast(new BigDecimal(s.getEastSt().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentSixMonthSt())) {
                                shipRent.setRentSixMonth(new BigDecimal(s.getRentSixMonthSt().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentTwelveMonthSt())) {
                                shipRent.setRentTwelveMonth(new BigDecimal(s.getRentTwelveMonthSt().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentThirtySixMonthSt())) {
                                shipRent.setRentThirtySixMonth(new BigDecimal(s.getRentThirtySixMonthSt().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentSixtyMonthSt())) {
                                shipRent.setRentSixtyMonth(new BigDecimal(s.getRentSixtyMonthSt().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentHundredTwentyMonthSt())) {
                                shipRent.setRentHundredTwentyMonth(new BigDecimal(s.getRentHundredTwentyMonthSt().replaceAll(",", "")));
                            }
                        } else if (i == 1) {
                            shipRent.setVesselType(2);
                            if (StringUtils.isNotBlank(s.getWestTFDE())) {
                                shipRent.setSpotWest(new BigDecimal(s.getWestTFDE().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getEastTFDE())) {
                                shipRent.setSpotEast(new BigDecimal(s.getEastTFDE().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentSixMonthTFDE())) {
                                shipRent.setRentSixMonth(new BigDecimal(s.getRentSixMonthTFDE().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentTwelveMonthTFDE())) {
                                shipRent.setRentTwelveMonth(new BigDecimal(s.getRentTwelveMonthTFDE().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentThirtySixMonthTFDE())) {
                                shipRent.setRentThirtySixMonth(new BigDecimal(s.getRentThirtySixMonthTFDE().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentSixtyMonthTFDE())) {
                                shipRent.setRentSixtyMonth(new BigDecimal(s.getRentSixtyMonthTFDE().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentHundredTwentyMonthTFDE())) {
                                shipRent.setRentHundredTwentyMonth(new BigDecimal(s.getRentHundredTwentyMonthTFDE().replaceAll(",", "")));
                            }
                        } else if (i == 2) {
                            shipRent.setVesselType(3);
                            if (StringUtils.isNotBlank(s.getWestXDF())) {
                                shipRent.setSpotWest(new BigDecimal(s.getWestXDF().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getEastXDF())) {
                                shipRent.setSpotEast(new BigDecimal(s.getEastXDF().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentSixMonthXDF())) {
                                shipRent.setRentSixMonth(new BigDecimal(s.getRentSixMonthXDF().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentTwelveMonthXDF())) {
                                shipRent.setRentTwelveMonth(new BigDecimal(s.getRentTwelveMonthXDF().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentThirtySixMonthXDF())) {
                                shipRent.setRentThirtySixMonth(new BigDecimal(s.getRentThirtySixMonthXDF().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentSixtyMonthXDF())) {
                                shipRent.setRentSixtyMonth(new BigDecimal(s.getRentSixtyMonthXDF().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentHundredTwentyMonthXDF())) {
                                shipRent.setRentHundredTwentyMonth(new BigDecimal(s.getRentHundredTwentyMonthXDF().replaceAll(",", "")));
                            }
                        }
                        this.save(shipRent);
                    }
                } else {
                     //更新已存在的数据
                    shipRents.stream().forEach(r->{
                        r.setUpdateBy(SecurityUtils.getStringUserId());
                        r.setUpdateDate(new Date());
                        if(r.getVesselType().equals(1)){
                            if (StringUtils.isNotBlank(s.getWestSt())) {
                                r.setSpotWest(new BigDecimal(s.getWestSt().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getEastSt())) {
                                r.setSpotEast(new BigDecimal(s.getEastSt().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentSixMonthSt())) {
                                r.setRentSixMonth(new BigDecimal(s.getRentSixMonthSt().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentTwelveMonthSt())) {
                                r.setRentTwelveMonth(new BigDecimal(s.getRentTwelveMonthSt().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentThirtySixMonthSt())) {
                                r.setRentThirtySixMonth(new BigDecimal(s.getRentThirtySixMonthSt().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentSixtyMonthSt())) {
                                r.setRentSixtyMonth(new BigDecimal(s.getRentSixtyMonthSt().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentHundredTwentyMonthSt())) {
                                r.setRentHundredTwentyMonth(new BigDecimal(s.getRentHundredTwentyMonthSt().replaceAll(",", "")));
                            }
                        }else if(r.getVesselType().equals(2)){
                            if (StringUtils.isNotBlank(s.getWestTFDE())) {
                                r.setSpotWest(new BigDecimal(s.getWestTFDE().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getEastTFDE())) {
                                r.setSpotEast(new BigDecimal(s.getEastTFDE().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentSixMonthTFDE())) {
                                r.setRentSixMonth(new BigDecimal(s.getRentSixMonthTFDE().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentTwelveMonthTFDE())) {
                                r.setRentTwelveMonth(new BigDecimal(s.getRentTwelveMonthTFDE().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentThirtySixMonthTFDE())) {
                                r.setRentThirtySixMonth(new BigDecimal(s.getRentThirtySixMonthTFDE().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentSixtyMonthTFDE())) {
                                r.setRentSixtyMonth(new BigDecimal(s.getRentSixtyMonthTFDE().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentHundredTwentyMonthTFDE())) {
                                r.setRentHundredTwentyMonth(new BigDecimal(s.getRentHundredTwentyMonthTFDE().replaceAll(",", "")));
                            }
                        }else if(r.getVesselType().equals(3)){
                            if (StringUtils.isNotBlank(s.getWestXDF())) {
                                r.setSpotWest(new BigDecimal(s.getWestXDF().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getEastXDF())) {
                                r.setSpotEast(new BigDecimal(s.getEastXDF().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentSixMonthXDF())) {
                                r.setRentSixMonth(new BigDecimal(s.getRentSixMonthXDF().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentTwelveMonthXDF())) {
                                r.setRentTwelveMonth(new BigDecimal(s.getRentTwelveMonthXDF().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentThirtySixMonthXDF())) {
                                r.setRentThirtySixMonth(new BigDecimal(s.getRentThirtySixMonthXDF().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentSixtyMonthXDF())) {
                                r.setRentSixtyMonth(new BigDecimal(s.getRentSixtyMonthXDF().replaceAll(",", "")));
                            }
                            if (StringUtils.isNotBlank(s.getRentHundredTwentyMonthXDF())) {
                                r.setRentHundredTwentyMonth(new BigDecimal(s.getRentHundredTwentyMonthXDF().replaceAll(",", "")));
                            }
                        }
                        this.updateById(r);
                    });
                }
            });
        }
    }

    /**
     * 导入保存船舶租金
     *
     * @param fobFileRecord
     */
    @Override
    public void saveShipRent(SpFobFileRecord fobFileRecord) {
        URL httpUrl = null;
        InputStream inputStream = null;
        try {
            String encodedFilePath = fobFileRecord.getPath().replace(" ", "%20");

            String CsoldPart = "http://10.75.34.224:9300/statics";
            String ZsoldPart = "http://10.75.33.149:9300/statics";
            String CsPart = "https://lngshippingcs.cgp.cnooc/api/file/statics";
            String ZsPart = "https://lngshipping.cgp.cnooc/api/file/statics";

            String newUrl = "";
            if (encodedFilePath.contains("https://lngshipping.cgp.cnooc")) {
                newUrl = encodedFilePath.replace(ZsPart, ZsoldPart);
            }else if (encodedFilePath.contains("https://lngshippingcs.cgp.cnooc")){
                newUrl = encodedFilePath.replace(CsPart, CsoldPart);
            }else {
                newUrl = encodedFilePath;
            }

            httpUrl = new URL(newUrl);
            HttpURLConnection urlConnection = (HttpURLConnection)httpUrl.openConnection();
            urlConnection.setRequestProperty("Content-Type", "application/x-www-form-urlencoded; charset=UTF-8");
            urlConnection.setRequestProperty("Accept", "text/html; charset=UTF-8");
            inputStream = urlConnection.getInputStream();
        } catch (MalformedURLException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        ShipRentImportListener importListener = new ShipRentImportListener(this);
        List<ReadSheet> readSheets = new ArrayList<>();
        ExcelReader builder = EasyExcel.read(inputStream).build();
        for (int i = 0; i < builder.excelExecutor().sheetList().size(); i++) {
            ReadSheet readSheet = EasyExcel.readSheet(i).head(ShipRentExcel.class).headRowNumber(3).registerReadListener(importListener).build();
            readSheets.add(readSheet);
        }
        //按照读取规则挨个读取
        builder.read(readSheets);
    }

    @Override
    public JsonResult addByVesselType(SmShipRent shipRent) {
        if (shipRent.getBroker() == null || shipRent.getRentDate() == null || shipRent.getVesselType() == null) {
            return JsonResult.failed("参数信息异常！");
        }
        if (shipRent.getSpotWest() == null && shipRent.getSpotEast() == null && shipRent.getSpotMiddleEast() == null
         && shipRent.getRentSixMonth() == null && shipRent.getRentTwelveMonth() == null && shipRent.getRentThirtySixMonth() == null
         && shipRent.getRentSixtyMonth() == null && shipRent.getRentHundredTwentyMonth() == null) {
            return JsonResult.failed("除Date 、Broker 、Vessel Type以外，其余必须至少要填一个");
        }
        shipRent.setCreateDate(new Date());
        shipRent.setCreateBy(SecurityUtils.getUsername());
        shipRent.setUpdateBy(shipRent.getCreateBy());
        shipRent.setUpdateDate(shipRent.getCreateDate());
        if (shipRent.getRentWeek() == null && shipRent.getRentDate() != null) {
            shipRent.setRentWeek(DateUtil.getWeekOfYear(shipRent.getRentDate()));
        }
        //校验数据重复
        SmShipRent oldData = checkRentData(shipRent);
        if(oldData == null){
            this.baseMapper.insert(shipRent);
        }else{
            BeanCopyUtils.copy(shipRent,oldData);
            this.baseMapper.updateById(oldData);
        }
        return JsonResult.success();
    }

    /**
     * 租金数据校验
     *
     * @param shipRent
     * @return
     */
    @Override
    public SmShipRent checkRentData(SmShipRent shipRent){
        //broker+Vessel tpye+date所属年+Week
        SmShipRent oldData = this.baseMapper.selectOne(
                new LambdaQueryWrapper<SmShipRent>()
                .eq(SmShipRent::getRentWeek,shipRent.getRentWeek())
                .eq(SmShipRent::getBroker,shipRent.getBroker())
                .eq(SmShipRent::getVesselType,shipRent.getVesselType())
                .apply("YEAR(rent_date) = {0}", CalculateDateUtil.getYears(shipRent.getRentDate()))
                .last("limit 1")
        );
        return oldData;
    }

    @Override
    public Object checkRentDataNew(SmShipRent shipRent) {
        CheckRentDataInfo checkRentDataInfo = new CheckRentDataInfo();
        //broker+Vessel tpye+date所属年+Week
        SmShipRent oldData = this.baseMapper.selectOne(
                new LambdaQueryWrapper<SmShipRent>()
                        .eq(SmShipRent::getRentWeek,shipRent.getRentWeek())
                        .eq(SmShipRent::getBroker,shipRent.getBroker())
                        .eq(SmShipRent::getVesselType,shipRent.getVesselType())
                        .apply("YEAR(rent_date) = {0}", CalculateDateUtil.getYears(shipRent.getRentDate()))
                        .last("limit 1")
        );
        if (null != oldData){
            checkRentDataInfo.setId(oldData.getId());
            checkRentDataInfo.setStatus(true);
        }else {
            checkRentDataInfo.setStatus(false);
        }
        return checkRentDataInfo;

    }

    /**
     * 编辑船舶租金
     *
     * @param shipRent
     * @return
     */
    @Override
    public SmShipRent updateShipRent(SmShipRent shipRent) {
        SmShipRent shipRentOld = this.getById(shipRent.getId());
        BeanUtils.copyProperties(shipRent, shipRentOld);
        shipRentOld.setUpdateDate(new Date());
        shipRentOld.setUpdateBy(SecurityUtils.getStringUserId());
        if (shipRentOld.getRentDate() != null) {
            shipRentOld.setRentWeek(DateUtil.getWeekOfYear(shipRentOld.getRentDate()));
        }
        this.updateById(shipRentOld);
        return shipRentOld;
    }

    @Override
    public JsonResult addByBroker(SmShipRentBrokerAddBo addBo) {
        if(addBo.getBroker() == null || addBo.getRentDate() == null){
            return JsonResult.failed("参数信息异常！");
        }
        if(addBo.getRentList().size() == 0){
            return JsonResult.failed("除Date和Broker以外，其余的租金必须要填至少一个");
        }
        List<SmShipRentBrokerListBo> addList = addBo.getRentList();
        List<SmShipRent> insertList = new LinkedList<>();
        for (SmShipRentBrokerListBo smShipRentBrokerListBo : addList) {
            SmShipRent smShipRent = new SmShipRent();
            BeanCopyUtils.copy(addBo,smShipRent);
            BeanCopyUtils.copy(smShipRentBrokerListBo,smShipRent);
            smShipRent.setCreateDate(new Date());
            smShipRent.setCreateBy(SecurityUtils.getUsername());
            smShipRent.setUpdateBy(smShipRent.getCreateBy());
            smShipRent.setUpdateDate(smShipRent.getCreateDate());
            SmShipRent oldData = checkRentData(smShipRent);
            if(oldData == null){
                insertList.add(smShipRent);
            }else{
                Long oldId = oldData.getId();
                BeanCopyUtils.copy(smShipRent,oldData);
                oldData.setId(oldId);
                insertList.add(oldData);
            }
        }
        this.saveOrUpdateBatch(insertList);
        return JsonResult.success();
    }

    @Override
    public Map<String,Object> summaryList(String key, String type) {
        Map<String,Object> resultMap = new HashedMap<>();
        List<SmShipRent> rentList = this.baseMapper.selectList(
                new LambdaQueryWrapper<SmShipRent>()
                .eq(StringUtils.equals("Broker",type),SmShipRent::getBroker,key)
        );
        Calendar calendar = Calendar.getInstance();
        for (SmShipRent smShipRent : rentList) {
            calendar.setTime(smShipRent.getRentDate());
            int year = calendar.get(Calendar.YEAR);
            String week = smShipRent.getRentWeek() < 10 ? "0"+smShipRent.getRentWeek() : String.valueOf(smShipRent.getRentWeek());
            smShipRent.setFormatDate(year+"-"+week);
        }

        List<JSONObject> headList = new LinkedList<>();
        //根据类型和数据集合组装表格头
        JSONObject weekJson = new JSONObject();
        weekJson.put("title","WEEK");
        weekJson.put("dataIndex","WEEK");
        weekJson.put("key","WEEK");
        weekJson.put("width",60);
        headList.add(weekJson);
        JSONObject dateJson = new JSONObject();
        dateJson.put("title","DATE");
        dateJson.put("dataIndex","DATE");
        dateJson.put("key","DATE");
        dateJson.put("width",60);
        headList.add(dateJson);

        JSONObject dataJson = new JSONObject();
        dataJson.put("title",key);

        List<String> vesselType = new LinkedList<>();
        vesselType.add("138-150k ST");
        vesselType.add("155-165k TFDE");
        vesselType.add("174k 2-Stroke/MEGI/XDF");

        List<JSONObject> dataArrayTwo = new LinkedList<>();
        if(StringUtils.equals("Broker",type)){
            List<String> areaList = new LinkedList<>();
            areaList.add("West");
            areaList.add("East");
            areaList.add("Average West And East");
            for (int i = 0; i < areaList.size(); i++) {
                JSONObject detailJson = new JSONObject();
                String parentKey = areaList.get(i);
                detailJson.put("title",areaList.get(i));
                List<JSONObject> detailArray = new LinkedList<>();
                for (String s : vesselType) {
                    JSONObject childJson = new JSONObject();
                    childJson.put("title",s);
                    childJson.put("dataIndex",parentKey+"-"+s);
                    childJson.put("key",parentKey+"-"+s);
                    childJson.put("width",200);
                    detailArray.add(childJson);
                }
                detailJson.put("children",detailArray);
                dataArrayTwo.add(detailJson);
            }
            dataJson.put("children",dataArrayTwo);
            headList.add(dataJson);
            resultMap.put("tableHead",headList);
            if(rentList.size() == 0){
                return resultMap;
            }

            Map<String,List<SmShipRent>> weekFormatMap = rentList.stream().collect(Collectors.groupingBy(SmShipRent::getFormatDate));
            weekFormatMap = weekFormatMap.entrySet().stream()
                    .sorted(Collections.reverseOrder(Map.Entry.comparingByKey()))
                    .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue, (e1, e2) -> e1, LinkedHashMap::new));
            //组装明细数据
            List<JSONObject> busDataList = new LinkedList<>();
            for (String formatDate : weekFormatMap.keySet()) {
                List<SmShipRent> weekList = weekFormatMap.get(formatDate);
                JSONObject busDetailJson = new JSONObject();
                busDetailJson.put("WEEK",weekList.get(0).getRentWeek());
                busDetailJson.put("DATE",DateUtils.format(weekList.get(0).getRentDate(),"yyyy-MM-dd"));

                BigDecimal westTypeOne = BigDecimal.ZERO;
                BigDecimal westTypeTwo = BigDecimal.ZERO;
                BigDecimal westTypeThree = BigDecimal.ZERO;
                BigDecimal eastTypeOne = BigDecimal.ZERO;
                BigDecimal eastTypeTwo = BigDecimal.ZERO;
                BigDecimal eastTypeThree = BigDecimal.ZERO;
                BigDecimal avgTypeOne = BigDecimal.ZERO;
                BigDecimal avgTypeTwo = BigDecimal.ZERO;
                BigDecimal avgTypeThree = BigDecimal.ZERO;
                for (SmShipRent smShipRent : weekList) {
                    if(1 == smShipRent.getVesselType()){
                        westTypeOne = westTypeOne.add(smShipRent.getSpotWest() != null ? smShipRent.getSpotWest() : BigDecimal.ZERO);
                        eastTypeOne = eastTypeOne.add(smShipRent.getSpotEast() != null ? smShipRent.getSpotEast() : BigDecimal.ZERO);
                    }
                    if(2 == smShipRent.getVesselType()){
                        westTypeTwo = westTypeTwo.add(smShipRent.getSpotWest() != null ? smShipRent.getSpotWest() : BigDecimal.ZERO);
                        eastTypeTwo = eastTypeTwo.add(smShipRent.getSpotEast() != null ? smShipRent.getSpotEast() : BigDecimal.ZERO);
                    }
                    if(3 == smShipRent.getVesselType()){
                        westTypeThree = westTypeThree.add(smShipRent.getSpotWest() != null ? smShipRent.getSpotWest() : BigDecimal.ZERO);
                        eastTypeThree = eastTypeThree.add(smShipRent.getSpotEast() != null ? smShipRent.getSpotEast() : BigDecimal.ZERO);
                    }
                }
                BigDecimal routeNum = new BigDecimal("2");
                avgTypeOne = (westTypeOne.add(eastTypeOne)).divide(routeNum);
                avgTypeTwo = (westTypeTwo.add(eastTypeTwo)).divide(routeNum);
                avgTypeThree = (westTypeThree.add(eastTypeThree)).divide(routeNum);

                busDetailJson.put("West-138-150k ST",westTypeOne.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(westTypeOne)+"$" : null);
                busDetailJson.put("West-155-165k TFDE",westTypeTwo.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(westTypeTwo)+"$" : null);
                busDetailJson.put("West-174k 2-Stroke/MEGI/XDF",westTypeThree.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(westTypeThree)+"$" : null);
                busDetailJson.put("East-138-150k ST",eastTypeOne.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(eastTypeOne)+"$" : null);
                busDetailJson.put("East-155-165k TFDE",eastTypeTwo.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(eastTypeTwo)+"$" : null);
                busDetailJson.put("East-174k 2-Stroke/MEGI/XDF",eastTypeThree.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(eastTypeThree)+"$" : null);
                busDetailJson.put("Average West And East-138-150k ST",avgTypeOne.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(avgTypeOne)+"$" : null);
                busDetailJson.put("Average West And East-155-165k TFDE",avgTypeTwo.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(avgTypeTwo)+"$" : null);
                busDetailJson.put("Average West And East-174k 2-Stroke/MEGI/XDF",avgTypeThree.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(avgTypeThree)+"$" : null);
                busDataList.add(busDetailJson);
            }
            resultMap.put("dataList",busDataList);
        }else if(StringUtils.equals("Area",type)){
            //获取所有的broker信息
            List<String> brokers = rentList.stream().map(SmShipRent::getBroker).collect(Collectors.toList());
            brokers = brokers.stream().distinct().collect(Collectors.toList());

            for (String vessel : vesselType) {
                JSONObject detailJson = new JSONObject();
                String parentKey = vessel;
                detailJson.put("title",vessel);
                List<JSONObject> detailArray = new LinkedList<>();
                for (String broker : brokers) {
                    JSONObject childJson = new JSONObject();
                    childJson.put("title",broker);
                    childJson.put("dataIndex",parentKey+"-"+broker);
                    childJson.put("key",parentKey+"-"+broker);
                    childJson.put("width",200);
                    detailArray.add(childJson);
                }
                //平均值
                JSONObject avgJson = new JSONObject();
                avgJson.put("title","Average of brokers");
                avgJson.put("dataIndex",parentKey+"-"+"Average of brokers");
                avgJson.put("key",parentKey+"-"+"Average of brokers");
                avgJson.put("width",200);
                detailArray.add(avgJson);

                detailJson.put("children",detailArray);
                dataArrayTwo.add(detailJson);
            }
            dataJson.put("children",dataArrayTwo);
            headList.add(dataJson);
            resultMap.put("tableHead",headList);

            Map<String,List<SmShipRent>> weekFormatMap = rentList.stream().collect(Collectors.groupingBy(SmShipRent::getFormatDate));
            weekFormatMap = weekFormatMap.entrySet().stream()
                    .sorted(Collections.reverseOrder(Map.Entry.comparingByKey()))
                    .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue, (e1, e2) -> e1, LinkedHashMap::new));
            //组装明细数据
            List<JSONObject> busDataList = new LinkedList<>();
            for (String integer : weekFormatMap.keySet()) {
                List<SmShipRent> weekList = weekFormatMap.get(integer);
                JSONObject busDetailJson = new JSONObject();
                busDetailJson.put("WEEK",weekList.get(0).getRentWeek());
                busDetailJson.put("DATE",DateUtils.format(weekList.get(0).getRentDate(),"yyyy-MM-dd"));

                Map<String,List<SmShipRent>> brokerMap = weekList.stream().collect(Collectors.groupingBy(SmShipRent::getBroker));
                BigDecimal brokerNum = new BigDecimal(String.valueOf(brokers.size()));

                int brokerNum138 = 0;
                int brokerNum155 = 0;
                int brokerNum174 = 0;

                BigDecimal totalSpot138 = BigDecimal.ZERO;
                BigDecimal totalSpot155 = BigDecimal.ZERO;
                BigDecimal totalSpot174 = BigDecimal.ZERO;
                for (String broker : brokers) {
                    if(brokerMap.get(broker) == null){
                        busDetailJson.put("138-150k ST-"+broker,null);
                        busDetailJson.put("155-165k TFDE-"+broker,null);
                        busDetailJson.put("174k 2-Stroke/MEGI/XDF-"+broker,null);
                        busDetailJson.put("138-150k ST-Average of brokers",null);
                        busDetailJson.put("155-165k TFDE-Average of brokers",null);
                        busDetailJson.put("174k 2-Stroke/MEGI/XDF-Average of brokers",null);
                        continue;
                    }
                    List<SmShipRent> brokerRentList = brokerMap.get(broker);
                    BigDecimal spot138 = BigDecimal.ZERO;
                    BigDecimal spot155 = BigDecimal.ZERO;
                    BigDecimal spot174 = BigDecimal.ZERO;
                    for (SmShipRent smShipRent : brokerRentList) {
                        BigDecimal spotWest = smShipRent.getSpotWest() != null ? smShipRent.getSpotWest() : BigDecimal.ZERO;
                        BigDecimal spotEast = smShipRent.getSpotEast() != null ? smShipRent.getSpotEast() : BigDecimal.ZERO;
                        if(1 == smShipRent.getVesselType()){
                            spot138 = spot138.add(StringUtils.equals("West",key) ? spotWest : spotEast);
                        }else if(2 == smShipRent.getVesselType()){
                            spot155 = spot155.add(StringUtils.equals("West",key) ? spotWest : spotEast);
                        }else{
                            spot174 = spot174.add(StringUtils.equals("West",key) ? spotWest : spotEast);
                        }
                    }
                    totalSpot138 = totalSpot138.add(spot138);
                    totalSpot155 = totalSpot155.add(spot155);
                    totalSpot174 = totalSpot174.add(spot174);

                    if(spot138.compareTo(BigDecimal.ZERO) > 0){
                        brokerNum138 ++;
                    }
                    if(spot155.compareTo(BigDecimal.ZERO) > 0){
                        brokerNum155 ++;
                    }
                    if(spot174.compareTo(BigDecimal.ZERO) > 0){
                        brokerNum174 ++;
                    }

                    busDetailJson.put("138-150k ST-"+broker,spot138.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(spot138)+"$" : null);
                    busDetailJson.put("155-165k TFDE-"+broker,spot155.compareTo(BigDecimal.ZERO) > 0 ?  NumberFormatUtil.numericFormatting(spot155)+"$" : null);
                    busDetailJson.put("174k 2-Stroke/MEGI/XDF-"+broker,spot174.compareTo(BigDecimal.ZERO) > 0 ?  NumberFormatUtil.numericFormatting(spot174)+"$" : null);
                }
                //平均值
                BigDecimal avgSpot138 = brokerNum138 == 0 ? BigDecimal.ZERO : totalSpot138.divide(new BigDecimal(String.valueOf(brokerNum138)), 0, RoundingMode.CEILING);
                BigDecimal avgSpot155 = brokerNum155 == 0 ? BigDecimal.ZERO : totalSpot155.divide(new BigDecimal(String.valueOf(brokerNum155)), 0, RoundingMode.CEILING);
                BigDecimal avgSpot174 = brokerNum174 == 0 ? BigDecimal.ZERO : totalSpot174.divide(new BigDecimal(String.valueOf(brokerNum174)), 0, RoundingMode.CEILING);
                busDetailJson.put("138-150k ST-Average of brokers",avgSpot138.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(avgSpot138)+"$" : null);
                busDetailJson.put("155-165k TFDE-Average of brokers",avgSpot155.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(avgSpot155)+"$" : null);
                busDetailJson.put("174k 2-Stroke/MEGI/XDF-Average of brokers",avgSpot174.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(avgSpot174)+"$" : null);
                busDataList.add(busDetailJson);
            }
            resultMap.put("dataList",busDataList);
        }else{
            //获取所有的broker信息
            List<String> brokers = rentList.stream().map(SmShipRent::getBroker).collect(Collectors.toList());
            brokers = brokers.stream().distinct().collect(Collectors.toList());
            brokers.add("Average of brokers");
            for (String broker : brokers) {
                JSONObject detailJson = new JSONObject();
                String parentKey = broker;
                detailJson.put("title",broker);
                List<JSONObject> detailArray = new LinkedList<>();
                for (String vessel : vesselType) {
                    JSONObject childJson = new JSONObject();
                    childJson.put("title",vessel);
                    childJson.put("dataIndex",parentKey+"-"+vessel);
                    childJson.put("key",parentKey+"-"+vessel);
                    childJson.put("width",200);
                    detailArray.add(childJson);
                }
                detailJson.put("children",detailArray);
                dataArrayTwo.add(detailJson);
            }
            dataJson.put("children",dataArrayTwo);
            headList.add(dataJson);
            resultMap.put("tableHead",headList);

            Map<String,List<SmShipRent>> weekFormatMap = rentList.stream().collect(Collectors.groupingBy(SmShipRent::getFormatDate));
            weekFormatMap = weekFormatMap.entrySet().stream()
                    .sorted(Collections.reverseOrder(Map.Entry.comparingByKey()))
                    .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue, (e1, e2) -> e1, LinkedHashMap::new));
            //组装明细数据
            List<JSONObject> busDataList = new LinkedList<>();
            for (String integer : weekFormatMap.keySet()) {
                List<SmShipRent> weekList = weekFormatMap.get(integer);
                JSONObject busDetailJson = new JSONObject();
                busDetailJson.put("WEEK",weekList.get(0).getRentWeek());
                busDetailJson.put("DATE",DateUtils.format(weekList.get(0).getRentDate(),"yyyy-MM-dd"));

                Map<String,List<SmShipRent>> brokerMap = weekList.stream().collect(Collectors.groupingBy(SmShipRent::getBroker));

                int brokerNum138 = 0;
                int brokerNum155 = 0;
                int brokerNum174 = 0;

                BigDecimal avgSpot138 = BigDecimal.ZERO;
                BigDecimal avgSpot155 = BigDecimal.ZERO;
                BigDecimal avgSpot174 = BigDecimal.ZERO;
                for (String broker : brokers) {
                    if(brokerMap.get(broker) == null){
                        busDetailJson.put(broker+"-138-150k ST",null);
                        busDetailJson.put(broker+"-155-165k TFDE",null);
                        busDetailJson.put(broker+"-174k 2-Stroke/MEGI/XDF",null);
                        continue;
                    }
                    List<SmShipRent> brokerRentList = brokerMap.get(broker);
                    BigDecimal totalSpot138 = BigDecimal.ZERO;
                    BigDecimal totalSpot155 = BigDecimal.ZERO;
                    BigDecimal totalSpot174 = BigDecimal.ZERO;
                    for (SmShipRent smShipRent : brokerRentList) {
                        BigDecimal spotSix = smShipRent.getRentSixMonth() != null ? smShipRent.getRentSixMonth() : BigDecimal.ZERO;
                        BigDecimal spotTwe = smShipRent.getRentTwelveMonth() != null ? smShipRent.getRentTwelveMonth() : BigDecimal.ZERO;
                        BigDecimal spotThi = smShipRent.getRentThirtySixMonth() != null ? smShipRent.getRentThirtySixMonth() : BigDecimal.ZERO;
                        BigDecimal spotSixty = smShipRent.getRentSixtyMonth() != null ? smShipRent.getRentSixtyMonth() : BigDecimal.ZERO;
                        BigDecimal spotTenYear = smShipRent.getRentHundredTwentyMonth() != null ? smShipRent.getRentHundredTwentyMonth() : BigDecimal.ZERO;
                        if(StringUtils.equals("6 Months",key)){
                            if(1 == smShipRent.getVesselType()){
                                totalSpot138 = totalSpot138.add(spotSix);
                            }else if(2 == smShipRent.getVesselType()){
                                totalSpot155 = totalSpot155.add(spotSix);
                            }else{
                                totalSpot174 = totalSpot174.add(spotSix);
                            }
                        }else if(StringUtils.equals("12 Months",key)){
                            if(1 == smShipRent.getVesselType()){
                                totalSpot138 = totalSpot138.add(spotTwe);
                            }else if(2 == smShipRent.getVesselType()){
                                totalSpot155 = totalSpot155.add(spotTwe);
                            }else{
                                totalSpot174 = totalSpot174.add(spotTwe);
                            }
                        }else if(StringUtils.equals("36 Months",key)){
                            if(1 == smShipRent.getVesselType()){
                                totalSpot138 = totalSpot138.add(spotThi);
                            }else if(2 == smShipRent.getVesselType()){
                                totalSpot155 = totalSpot155.add(spotThi);
                            }else{
                                totalSpot174 = totalSpot174.add(spotThi);
                            }
                        }else if(StringUtils.equals("5 Years",key)){
                            if(1 == smShipRent.getVesselType()){
                                totalSpot138 = totalSpot138.add(spotSixty);
                            }else if(2 == smShipRent.getVesselType()){
                                totalSpot155 = totalSpot155.add(spotSixty);
                            }else{
                                totalSpot174 = totalSpot174.add(spotSixty);
                            }
                        }else{
                            if(1 == smShipRent.getVesselType()){
                                totalSpot138 = totalSpot138.add(spotTenYear);
                            }else if(2 == smShipRent.getVesselType()){
                                totalSpot155 = totalSpot155.add(spotTenYear);
                            }else{
                                totalSpot174 = totalSpot174.add(spotTenYear);
                            }
                        }
                    }

                    if(totalSpot138.compareTo(BigDecimal.ZERO) > 0){
                        brokerNum138 ++;
                    }
                    if(totalSpot155.compareTo(BigDecimal.ZERO) > 0){
                        brokerNum155 ++;
                    }
                    if(totalSpot174.compareTo(BigDecimal.ZERO) > 0){
                        brokerNum174 ++;
                    }

                    avgSpot138 = avgSpot138.add(totalSpot138);
                    avgSpot155 = avgSpot155.add(totalSpot155);
                    avgSpot174 = avgSpot174.add(totalSpot174);
                    busDetailJson.put(broker+"-138-150k ST",totalSpot138.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(totalSpot138)+"$" : null);
                    busDetailJson.put(broker+"-155-165k TFDE",totalSpot155.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(totalSpot155)+"$" : null);
                    busDetailJson.put(broker+"-174k 2-Stroke/MEGI/XDF",totalSpot174.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(totalSpot174)+"$" : null);
                }

                //平均值
                avgSpot138 = brokerNum138 > 0 ? avgSpot138.divide(new BigDecimal(String.valueOf(brokerNum138)), 0, RoundingMode.CEILING) : BigDecimal.ZERO;
                avgSpot155 = brokerNum155 > 0 ? avgSpot155.divide(new BigDecimal(String.valueOf(brokerNum155)), 0, RoundingMode.CEILING) : BigDecimal.ZERO;
                avgSpot174 = brokerNum174 > 0 ? avgSpot174.divide(new BigDecimal(String.valueOf(brokerNum174)), 0, RoundingMode.CEILING) : BigDecimal.ZERO;
                busDetailJson.put("Average of brokers-138-150k ST",avgSpot138.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(avgSpot138)+"$" : null);
                busDetailJson.put("Average of brokers-155-165k TFDE",avgSpot155.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(avgSpot155)+"$" : null);
                busDetailJson.put("Average of brokers-174k 2-Stroke/MEGI/XDF",avgSpot174.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(avgSpot174)+"$" : null);

                busDataList.add(busDetailJson);
            }
            resultMap.put("dataList",busDataList);
        }
        return resultMap;
    }

    /**
     * 按Term导出
     * @param rentList
     * @param key
     * @return
     */
    public String summaryTermExport(List<SmShipRent> rentList,String key) throws IOException {
        List<String> vesselType = new LinkedList<>();
        vesselType.add("138-150k ST");
        vesselType.add("155-165k TFDE");
        vesselType.add("174k 2-Stroke/MEGI/XDF");

        // 创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        String sheetName = key+"租金汇总";
        // 创建一个工作表
        Sheet sheet = workbook.createSheet(sheetName);
        // 创建一个行
        Row row = sheet.createRow(0);
        // 创建一个单元格并设置值
        Cell cell = row.createCell(0);
        cell.setCellValue("WEEK");
        Cell cell1 = row.createCell(1);
        cell1.setCellValue("DATE");
        Cell cell2 = row.createCell(2);
        cell2.setCellValue(key);

        List<String> brokers = rentList.stream().map(SmShipRent::getBroker).collect(Collectors.toList());
        brokers = brokers.stream().distinct().collect(Collectors.toList());

        // 创建一个行
        Row row1 = sheet.createRow(1);
        Row row2 = sheet.createRow(2);
        int twoNum = 2;
        brokers.add("Average of brokers");
        for (int i = 0; i < brokers.size(); i++) {
            Cell r1cell = row1.createCell(twoNum);
            r1cell.setCellValue(brokers.get(i));
            for (int i1 = 0; i1 < vesselType.size(); i1++) {
                Cell r2cell = row2.createCell(twoNum+i1);
                r2cell.setCellValue(vesselType.get(i1));
            }

            CellRangeAddress region = new CellRangeAddress(1, 1, twoNum, twoNum+vesselType.size()-1);
            sheet.addMergedRegion(region);
            setRegionStyle(sheet, region, cellStyle);
            twoNum = (twoNum + vesselType.size());
        }

        // 合并单元格
        CellRangeAddress weekRegion = new CellRangeAddress(0, 2, 0, 0);
        sheet.addMergedRegion(weekRegion);
        setRegionStyle(sheet, weekRegion, cellStyle);
        CellRangeAddress dateRegion = new CellRangeAddress(0, 2, 1, 1);
        sheet.addMergedRegion(dateRegion);
        setRegionStyle(sheet, dateRegion, cellStyle);
        CellRangeAddress topRegion = new CellRangeAddress(0, 0, 2, twoNum-1);
        sheet.addMergedRegion(topRegion);
        setRegionStyle(sheet, topRegion, cellStyle);

        Map<String,List<SmShipRent>> weekFormatMap = rentList.stream().collect(Collectors.groupingBy(SmShipRent::getFormatDate));
        weekFormatMap = weekFormatMap.entrySet().stream()
                .sorted(Collections.reverseOrder(Map.Entry.comparingByKey()))
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue, (e1, e2) -> e1, LinkedHashMap::new));
        //组装明细数据
        List<JSONObject> busDataList = new LinkedList<>();
        for (String integer : weekFormatMap.keySet()) {
            List<SmShipRent> weekList = weekFormatMap.get(integer);
            JSONObject busDetailJson = new JSONObject();
            busDetailJson.put("WEEK",integer.toString());
            busDetailJson.put("DATE",DateUtils.format(weekList.get(0).getRentDate(),"yyyy-MM-dd"));

            Map<String,List<SmShipRent>> brokerMap = weekList.stream().collect(Collectors.groupingBy(SmShipRent::getBroker));
            BigDecimal brokerNum = new BigDecimal(String.valueOf(brokers.size()-1));
            BigDecimal avgSpot138 = BigDecimal.ZERO;
            BigDecimal avgSpot155 = BigDecimal.ZERO;
            BigDecimal avgSpot174 = BigDecimal.ZERO;
            for (String broker : brokers) {
                if(brokerMap.get(broker) == null){
                    busDetailJson.put(broker+"-138-150k ST",null);
                    busDetailJson.put(broker+"-155-165k TFDE",null);
                    busDetailJson.put(broker+"-174k 2-Stroke/MEGI/XDF",null);
                    continue;
                }
                List<SmShipRent> brokerRentList = brokerMap.get(broker);
                BigDecimal totalSpot138 = BigDecimal.ZERO;
                BigDecimal totalSpot155 = BigDecimal.ZERO;
                BigDecimal totalSpot174 = BigDecimal.ZERO;
                for (SmShipRent smShipRent : brokerRentList) {
                    BigDecimal spotSix = smShipRent.getRentSixMonth() != null ? smShipRent.getRentSixMonth() : BigDecimal.ZERO;
                    BigDecimal spotTwe = smShipRent.getRentTwelveMonth() != null ? smShipRent.getRentTwelveMonth() : BigDecimal.ZERO;
                    BigDecimal spotThi = smShipRent.getRentThirtySixMonth() != null ? smShipRent.getRentThirtySixMonth() : BigDecimal.ZERO;
                    BigDecimal spotSixty = smShipRent.getRentSixtyMonth() != null ? smShipRent.getRentSixtyMonth() : BigDecimal.ZERO;
                    if(StringUtils.equals("6 Months",key)){
                        if(1 == smShipRent.getVesselType()){
                            totalSpot138 = totalSpot138.add(spotSix);
                        }else if(2 == smShipRent.getVesselType()){
                            totalSpot155 = totalSpot155.add(spotSix);
                        }else{
                            totalSpot174 = totalSpot174.add(spotSix);
                        }
                    }else if(StringUtils.equals("12 Months",key)){
                        if(1 == smShipRent.getVesselType()){
                            totalSpot138 = totalSpot138.add(spotTwe);
                        }else if(2 == smShipRent.getVesselType()){
                            totalSpot155 = totalSpot155.add(spotTwe);
                        }else{
                            totalSpot174 = totalSpot174.add(spotTwe);
                        }
                    }else if(StringUtils.equals("36 Months",key)){
                        if(1 == smShipRent.getVesselType()){
                            totalSpot138 = totalSpot138.add(spotThi);
                        }else if(2 == smShipRent.getVesselType()){
                            totalSpot155 = totalSpot155.add(spotThi);
                        }else{
                            totalSpot174 = totalSpot174.add(spotThi);
                        }
                    }else{
                        if(1 == smShipRent.getVesselType()){
                            totalSpot138 = totalSpot138.add(spotSixty);
                        }else if(2 == smShipRent.getVesselType()){
                            totalSpot155 = totalSpot155.add(spotSixty);
                        }else{
                            totalSpot174 = totalSpot174.add(spotSixty);
                        }
                    }
                }
                avgSpot138 = avgSpot138.add(totalSpot138);
                avgSpot155 = avgSpot155.add(totalSpot155);
                avgSpot174 = avgSpot174.add(totalSpot174);
                busDetailJson.put(broker+"-138-150k ST",NumberFormatUtil.numericFormatting(totalSpot138));
                busDetailJson.put(broker+"-155-165k TFDE",NumberFormatUtil.numericFormatting(totalSpot155));
                busDetailJson.put(broker+"-174k 2-Stroke/MEGI/XDF",NumberFormatUtil.numericFormatting(totalSpot174));
            }
            //平均值
            avgSpot138 = avgSpot138.divide(brokerNum, 0, RoundingMode.CEILING);
            avgSpot155 = avgSpot155.divide(brokerNum, 0, RoundingMode.CEILING);
            avgSpot174 = avgSpot174.divide(brokerNum, 0, RoundingMode.CEILING);
            busDetailJson.put("Average of brokers-138-150k ST",NumberFormatUtil.numericFormatting(avgSpot138));
            busDetailJson.put("Average of brokers-155-165k TFDE",NumberFormatUtil.numericFormatting(avgSpot155));
            busDetailJson.put("Average of brokers-174k 2-Stroke/MEGI/XDF",NumberFormatUtil.numericFormatting(avgSpot174));
            busDataList.add(busDetailJson);
        }

        for (int i = 0; i < busDataList.size(); i++) {
            JSONObject bo = busDataList.get(i);
            Row dataRow = sheet.createRow(i+3);
            dataRow.createCell(0).setCellValue(bo.getString("WEEK"));
            dataRow.createCell(1).setCellValue(bo.getString("DATE"));
            int dataNum = 2;

            for (String broker : brokers) {
                for (String vessel : vesselType) {
                    dataRow.createCell(dataNum).setCellValue(bo.getString(broker+"-"+vessel));
                    dataNum ++;
                }
            }
        }

        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j);
                    }
                    cell.setCellStyle(cellStyle);
                }
            }
        }

        //设置后续数据的列宽
        sheet.setColumnWidth(1,6000);
        for (int i = 2; i < twoNum; i++) {
            sheet.setColumnWidth(i,6000);
        }

        // 获取当前日期
        LocalDateTime currentDate = LocalDateTime.now();
        // 格式化日期为指定格式（例如：年月日）
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy.MM.dd HH.mm");
        String formattedDate = currentDate.format(formatter);
        String excelName = (sheetName+formattedDate+".xlsx");
        try (FileOutputStream fileOut = new FileOutputStream(excelName)) {
            workbook.write(fileOut);
            fileOut.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //文件地址
        File file = new File(excelName);
//        //声明参数集合
//        HashMap<String, Object> paramMap = new HashMap<>();
//        //文件
//        paramMap.put("file", file);
//        //输出
//        paramMap.put("output","json");
//        //自定义路径
//        paramMap.put("path","image");
//        //场景
//        paramMap.put("scene","image");
//
//        paramMap.put("filename",excelName);
        //上传
//        String result= HttpUtil.post(fileServerHost+"/group1/upload", paramMap);

        FileInputStream input = new FileInputStream(file);
        MultipartFile multipartFile = new MockMultipartFile("file", file.getName(), "application/octet-stream", input);
        R<SysFile> result1 = remoteFileService.upload(multipartFile);
        if(result1.getCode() != R.SUCCESS){
            throw new BusException("文件服务器连接失败", BaseResultCode.GENERAL_ERROR);
        }
        String url = result1.getData().getHttpsUrl();
//        System.out.println("数据导出文件上传结果日志打印："+result);
        if (file.exists()) {
            if (file.delete()) {
            }
        }
        // 使用Gson解析JSON
//        Gson gson = new Gson();
//        JsonObject jsonObject = gson.fromJson(result, JsonObject.class);
//        // 获取"url"字段的值
//        String url = jsonObject.get("url").getAsString();
        return url;
    }

    /**
     * 按Area导出
     * @param rentList
     * @param key
     * @return
     */
    public String summaryAreaExport(List<SmShipRent> rentList,String key) throws IOException {
        List<String> vesselType = new LinkedList<>();
        vesselType.add("138-150k ST");
        vesselType.add("155-165k TFDE");
        vesselType.add("174k 2-Stroke/MEGI/XDF");

        // 创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        String sheetName = key+"租金汇总";
        // 创建一个工作表
        Sheet sheet = workbook.createSheet(sheetName);
        // 创建一个行
        Row row = sheet.createRow(0);
        // 创建一个单元格并设置值
        Cell cell = row.createCell(0);
        cell.setCellValue("WEEK");
        Cell cell1 = row.createCell(1);
        cell1.setCellValue("DATE");
        Cell cell2 = row.createCell(2);
        cell2.setCellValue(key);

        List<String> brokers = rentList.stream().map(SmShipRent::getBroker).collect(Collectors.toList());
        brokers = brokers.stream().distinct().collect(Collectors.toList());

        // 创建一个行
        Row row1 = sheet.createRow(1);
        Row row2 = sheet.createRow(2);
        int twoNum = 2;
        for (int i = 0; i < vesselType.size(); i++) {
            Cell r1cell = row1.createCell(twoNum);
            r1cell.setCellValue(vesselType.get(i));
            for (int i1 = 0; i1 < brokers.size(); i1++) {
                Cell r2cell = row2.createCell(twoNum+i1);
                r2cell.setCellValue(brokers.get(i1));
            }
            //平均值
            Cell r2AvgCell = row2.createCell(twoNum+brokers.size());
            r2AvgCell.setCellValue("Average of brokers");

            CellRangeAddress region = new CellRangeAddress(1, 1, twoNum, twoNum+brokers.size());
            sheet.addMergedRegion(region);
            setRegionStyle(sheet, region, cellStyle);
            twoNum = (twoNum + brokers.size() + 1);
        }

        // 合并单元格
        CellRangeAddress weekRegion = new CellRangeAddress(0, 2, 0, 0);
        sheet.addMergedRegion(weekRegion);
        setRegionStyle(sheet, weekRegion, cellStyle);
        CellRangeAddress dateRegion = new CellRangeAddress(0, 2, 1, 1);
        sheet.addMergedRegion(dateRegion);
        setRegionStyle(sheet, dateRegion, cellStyle);
        CellRangeAddress topRegion = new CellRangeAddress(0, 0, 2, twoNum-1);
        sheet.addMergedRegion(topRegion);
        setRegionStyle(sheet, topRegion, cellStyle);

        Map<String,List<SmShipRent>> weekFormatMap = rentList.stream().collect(Collectors.groupingBy(SmShipRent::getFormatDate));
        weekFormatMap = weekFormatMap.entrySet().stream()
                .sorted(Collections.reverseOrder(Map.Entry.comparingByKey()))
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue, (e1, e2) -> e1, LinkedHashMap::new));
        //组装明细数据
        List<JSONObject> busDataList = new LinkedList<>();
        for (String integer : weekFormatMap.keySet()) {
            List<SmShipRent> weekList = weekFormatMap.get(integer);
            JSONObject busDetailJson = new JSONObject();
            busDetailJson.put("WEEK",integer.toString());
            busDetailJson.put("DATE",DateUtils.format(weekList.get(0).getRentDate(),"yyyy-MM-dd"));

            Map<String,List<SmShipRent>> brokerMap = weekList.stream().collect(Collectors.groupingBy(SmShipRent::getBroker));
            BigDecimal brokerNum = new BigDecimal(String.valueOf(brokers.size()));
            BigDecimal totalSpot138 = BigDecimal.ZERO;
            BigDecimal totalSpot155 = BigDecimal.ZERO;
            BigDecimal totalSpot174 = BigDecimal.ZERO;
            for (String broker : brokers) {
                if(brokerMap.get(broker) == null){
                    busDetailJson.put("138-150k ST-"+broker,null);
                    busDetailJson.put("155-165k TFDE-"+broker,null);
                    busDetailJson.put("174k 2-Stroke/MEGI/XDF-"+broker,null);
                    busDetailJson.put("174k 2-Stroke/MEGI/XDF-Average of brokers",null);
                    continue;
                }
                List<SmShipRent> brokerRentList = brokerMap.get(broker);
                BigDecimal spot138 = BigDecimal.ZERO;
                BigDecimal spot155 = BigDecimal.ZERO;
                BigDecimal spot174 = BigDecimal.ZERO;
                for (SmShipRent smShipRent : brokerRentList) {
                    BigDecimal spotWest = smShipRent.getSpotWest() != null ? smShipRent.getSpotWest() : BigDecimal.ZERO;
                    BigDecimal spotEast = smShipRent.getSpotEast() != null ? smShipRent.getSpotEast() : BigDecimal.ZERO;
                    if(1 == smShipRent.getVesselType()){
                        spot138 = spot138.add(StringUtils.equals("West",key) ? spotWest : spotEast);
                    }else if(2 == smShipRent.getVesselType()){
                        spot155 = spot155.add(StringUtils.equals("West",key) ? spotWest : spotEast);
                    }else{
                        spot174 = spot174.add(StringUtils.equals("West",key) ? spotWest : spotEast);
                    }
                }
                busDetailJson.put("138-150k ST-"+broker,NumberFormatUtil.numericFormatting(spot138));
                busDetailJson.put("155-165k TFDE-"+broker,NumberFormatUtil.numericFormatting(spot155));
                busDetailJson.put("174k 2-Stroke/MEGI/XDF-"+broker,NumberFormatUtil.numericFormatting(spot174));

                totalSpot138 = totalSpot138.add(spot138);
                totalSpot155 = totalSpot155.add(spot155);
                totalSpot174 = totalSpot174.add(spot174);
            }
            //平均值
            BigDecimal avgSpot138 = totalSpot138.divide(brokerNum, 0, RoundingMode.CEILING);
            BigDecimal avgSpot155 = totalSpot155.divide(brokerNum, 0, RoundingMode.CEILING);
            BigDecimal avgSpot174 = totalSpot174.divide(brokerNum, 0, RoundingMode.CEILING);
            busDetailJson.put("138-150k ST-Average of brokers",NumberFormatUtil.numericFormatting(avgSpot138));
            busDetailJson.put("155-165k TFDE-Average of brokers",NumberFormatUtil.numericFormatting(avgSpot155));
            busDetailJson.put("174k 2-Stroke/MEGI/XDF-Average of brokers",NumberFormatUtil.numericFormatting(avgSpot174));
            busDataList.add(busDetailJson);
        }

        for (int i = 0; i < busDataList.size(); i++) {
            JSONObject bo = busDataList.get(i);
            Row dataRow = sheet.createRow(i+3);
            dataRow.createCell(0).setCellValue(bo.getString("WEEK"));
            dataRow.createCell(1).setCellValue(bo.getString("DATE"));
            int dataNum = 2;
            for (String vessel : vesselType) {
                for (int j = 0; j < brokers.size(); j++) {
                    dataRow.createCell(dataNum).setCellValue(bo.getString(vessel+"-"+brokers.get(j)));
                    dataNum ++;
                }
                //平均值
                dataRow.createCell(dataNum).setCellValue(bo.getString(vessel+"-Average of brokers"));
                dataNum ++;
            }
        }

        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j);
                    }
                    cell.setCellStyle(cellStyle);
                }
            }
        }

        //设置后续数据的列宽
        sheet.setColumnWidth(1,6000);
        for (int i = 2; i < twoNum; i++) {
            sheet.setColumnWidth(i,6000);
        }

        // 获取当前日期
        LocalDateTime currentDate = LocalDateTime.now();
        // 格式化日期为指定格式（例如：年月日）
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy.MM.dd HH.mm");
        String formattedDate = currentDate.format(formatter);
        String excelName = (sheetName+formattedDate+".xlsx");
        try (FileOutputStream fileOut = new FileOutputStream(excelName)) {
            workbook.write(fileOut);
            fileOut.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //文件地址
        File file = new File(excelName);
//        //声明参数集合
//        HashMap<String, Object> paramMap = new HashMap<>();
//        //文件
//        paramMap.put("file", file);
//        //输出
//        paramMap.put("output","json");
//        //自定义路径
//        paramMap.put("path","image");
//        //场景
//        paramMap.put("scene","image");
//
//        paramMap.put("filename",excelName);
        //上传
//        String result= HttpUtil.post(fileServerHost+"/group1/upload", paramMap);
//        System.out.println("数据导出文件上传结果日志打印："+result);
        FileInputStream input = new FileInputStream(file);
        MultipartFile multipartFile = new MockMultipartFile("file", file.getName(), "application/octet-stream", input);
        R<SysFile> result1 = remoteFileService.upload(multipartFile);
        if(result1.getCode() != R.SUCCESS){
            throw new BusException("文件服务器连接失败", BaseResultCode.GENERAL_ERROR);
        }
        String url = result1.getData().getHttpsUrl();

        File excelFile = new File(excelName);
        if (excelFile.exists()) {
            if (excelFile.delete()) {
            }
        }
//        // 使用Gson解析JSON
//        Gson gson = new Gson();
//        JsonObject jsonObject = gson.fromJson(result, JsonObject.class);
//        // 获取"url"字段的值
//        String url = jsonObject.get("url").getAsString();
        return url;
    }

    /**
     * 按broker导出
     * @param rentList
     * @param key
     * @return
     */
    public String summaryBrokerExport(List<SmShipRent> rentList,String key) throws IOException {
        List<String> vesselType = new LinkedList<>();
        vesselType.add("138-150k ST");
        vesselType.add("155-165k TFDE");
        vesselType.add("174k 2-Stroke/MEGI/XDF");
        List<String> areaList = new LinkedList<>();
        areaList.add("West");
        areaList.add("East");
        areaList.add("Average West And East");

        // 创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        String sheetName = key+"租金汇总";
        // 创建一个工作表
        Sheet sheet = workbook.createSheet(sheetName);
        // 创建一个行
        Row row = sheet.createRow(0);
        // 创建一个单元格并设置值
        Cell cell = row.createCell(0);
        cell.setCellValue("WEEK");
        Cell cell1 = row.createCell(1);
        cell1.setCellValue("DATE");
        Cell cell2 = row.createCell(2);
        cell2.setCellValue(key);

        // 创建一个行
        Row row1 = sheet.createRow(1);
        Row row2 = sheet.createRow(2);
        int twoNum = 2;
        for (int i = 0; i < areaList.size(); i++) {
            Cell r1cell = row1.createCell(twoNum);
            r1cell.setCellValue(areaList.get(i));
            for (int i1 = 0; i1 < vesselType.size(); i1++) {
                Cell r2cell = row2.createCell(twoNum+i1);
                r2cell.setCellValue(vesselType.get(i1));
            }
            CellRangeAddress region = new CellRangeAddress(1, 1, twoNum, twoNum+vesselType.size()-1);
            sheet.addMergedRegion(region);
            setRegionStyle(sheet, region, cellStyle);
            twoNum = (twoNum + 3);
        }

        // 合并单元格
        CellRangeAddress weekRegion = new CellRangeAddress(0, 2, 0, 0);
        sheet.addMergedRegion(weekRegion);
        setRegionStyle(sheet, weekRegion, cellStyle);
        CellRangeAddress dateRegion = new CellRangeAddress(0, 2, 1, 1);
        sheet.addMergedRegion(dateRegion);
        setRegionStyle(sheet, dateRegion, cellStyle);
        CellRangeAddress topRegion = new CellRangeAddress(0, 0, 2, twoNum-1);
        sheet.addMergedRegion(topRegion);
        setRegionStyle(sheet, topRegion, cellStyle);

        List<SmShipRentBrokerSummaryListBo> resultList = new LinkedList<>();
        Map<String,List<SmShipRent>> weekFormatMap = rentList.stream().collect(Collectors.groupingBy(SmShipRent::getFormatDate));
        weekFormatMap = weekFormatMap.entrySet().stream()
                .sorted(Collections.reverseOrder(Map.Entry.comparingByKey()))
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue, (e1, e2) -> e1, LinkedHashMap::new));
        for (String integer : weekFormatMap.keySet()) {
            SmShipRentBrokerSummaryListBo summaryListBo = new SmShipRentBrokerSummaryListBo();
            List<SmShipRent> weekList = weekFormatMap.get(integer);
            summaryListBo.setRentWeek(integer.toString());

            summaryListBo.setRentDate(DateUtils.format(weekList.get(0).getRentDate(),"yyyy-MM-dd"));

            BigDecimal westTypeOne = BigDecimal.ZERO;
            BigDecimal westTypeTwo = BigDecimal.ZERO;
            BigDecimal westTypeThree = BigDecimal.ZERO;
            BigDecimal eastTypeOne = BigDecimal.ZERO;
            BigDecimal eastTypeTwo = BigDecimal.ZERO;
            BigDecimal eastTypeThree = BigDecimal.ZERO;
            BigDecimal avgTypeOne = BigDecimal.ZERO;
            BigDecimal avgTypeTwo = BigDecimal.ZERO;
            BigDecimal avgTypeThree = BigDecimal.ZERO;
            for (SmShipRent smShipRent : weekList) {
                if(1 == smShipRent.getVesselType()){
                    westTypeOne = westTypeOne.add(smShipRent.getSpotWest() != null ? smShipRent.getSpotWest() : BigDecimal.ZERO);
                    eastTypeOne = eastTypeOne.add(smShipRent.getSpotEast() != null ? smShipRent.getSpotEast() : BigDecimal.ZERO);
                }
                if(2 == smShipRent.getVesselType()){
                    westTypeTwo = westTypeTwo.add(smShipRent.getSpotWest() != null ? smShipRent.getSpotWest() : BigDecimal.ZERO);
                    eastTypeTwo = eastTypeTwo.add(smShipRent.getSpotEast() != null ? smShipRent.getSpotEast() : BigDecimal.ZERO);
                }
                if(3 == smShipRent.getVesselType()){
                    westTypeThree = westTypeThree.add(smShipRent.getSpotWest() != null ? smShipRent.getSpotWest() : BigDecimal.ZERO);
                    eastTypeThree = eastTypeThree.add(smShipRent.getSpotEast() != null ? smShipRent.getSpotEast() : BigDecimal.ZERO);
                }
            }
            BigDecimal routeNum = new BigDecimal("2");
            avgTypeOne = (westTypeOne.add(eastTypeOne)).divide(routeNum);
            avgTypeTwo = (westTypeTwo.add(eastTypeTwo)).divide(routeNum);
            avgTypeThree = (westTypeThree.add(eastTypeThree)).divide(routeNum);

            summaryListBo.setWestTypeOne(westTypeOne.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(westTypeOne) : null);
            summaryListBo.setWestTypeTwo(westTypeTwo.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(westTypeTwo) : null);
            summaryListBo.setWestTypeThree(westTypeThree.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(westTypeThree) : null);
            summaryListBo.setEastTypeOne(eastTypeOne.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(eastTypeOne) : null);
            summaryListBo.setEastTypeTwo(eastTypeTwo.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(eastTypeTwo) : null);
            summaryListBo.setEastTypeThree(eastTypeThree.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(eastTypeThree) : null);
            summaryListBo.setAvgTypeOne(avgTypeOne.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(avgTypeOne) : null);
            summaryListBo.setAvgTypeTwo(avgTypeTwo.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(avgTypeTwo) : null);
            summaryListBo.setAvgTypeThree(avgTypeThree.compareTo(BigDecimal.ZERO) > 0 ? NumberFormatUtil.numericFormatting(avgTypeThree) : null);
            resultList.add(summaryListBo);
        }

        for (int i = 0; i < resultList.size(); i++) {
            SmShipRentBrokerSummaryListBo bo = resultList.get(i);
            Row dataRow = sheet.createRow(i+3);
            dataRow.createCell(0).setCellValue(bo.getRentWeek());
            dataRow.createCell(1).setCellValue(bo.getRentDate());
            dataRow.createCell(2).setCellValue(bo.getWestTypeOne());
            dataRow.createCell(3).setCellValue(bo.getWestTypeTwo());
            dataRow.createCell(4).setCellValue(bo.getWestTypeThree());
            dataRow.createCell(5).setCellValue(bo.getEastTypeOne());
            dataRow.createCell(6).setCellValue(bo.getEastTypeTwo());
            dataRow.createCell(7).setCellValue(bo.getEastTypeThree());
            dataRow.createCell(8).setCellValue(bo.getAvgTypeOne());
            dataRow.createCell(9).setCellValue(bo.getAvgTypeTwo());
            dataRow.createCell(10).setCellValue(bo.getAvgTypeThree());
        }

        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j);
                    }
                    cell.setCellStyle(cellStyle);
                }
            }
        }

        //设置后续数据的列宽
        sheet.setColumnWidth(1,6000);
        for (int i = 2; i < twoNum; i++) {
            sheet.setColumnWidth(i,6000);
        }

        // 获取当前日期
        LocalDateTime currentDate = LocalDateTime.now();
        // 格式化日期为指定格式（例如：年月日）
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy.MM.dd HH.mm");
        String formattedDate = currentDate.format(formatter);
        String excelName = (sheetName+formattedDate+".xlsx");
        try (FileOutputStream fileOut = new FileOutputStream(excelName)) {
            workbook.write(fileOut);
            fileOut.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //文件地址
        File file = new File(excelName);
//        //声明参数集合
//        HashMap<String, Object> paramMap = new HashMap<>();
//        //文件
//        paramMap.put("file", file);
//        //输出
//        paramMap.put("output","json");
//        //自定义路径
//        paramMap.put("path","image");
//        //场景
//        paramMap.put("scene","image");
//
//        paramMap.put("filename",excelName);

        FileInputStream input = new FileInputStream(file);
        MultipartFile multipartFile = new MockMultipartFile("file", file.getName(), "application/octet-stream", input);
        R<SysFile> result1 = remoteFileService.upload(multipartFile);
        if(result1.getCode() != R.SUCCESS){
            throw new BusException("文件服务器连接失败", BaseResultCode.GENERAL_ERROR);
        }
        String url = result1.getData().getHttpsUrl();
        //上传
//        String result= HttpUtil.post(fileServerHost+"/group1/upload", paramMap);
//        System.out.println("数据导出文件上传结果日志打印："+result);
        File excelFile = new File(excelName);
        if (excelFile.exists()) {
            if (excelFile.delete()) {
            }
        }
//        // 使用Gson解析JSON
//        Gson gson = new Gson();
//        JsonObject jsonObject = gson.fromJson(result, JsonObject.class);
//        // 获取"url"字段的值
//        String url = jsonObject.get("url").getAsString();
        return url;
    }

    @Override
    public JsonResult<String> summaryListExport(String key, String type) throws IOException {
        List<SmShipRent> rentList = this.baseMapper.selectList(
                new LambdaQueryWrapper<SmShipRent>()
                        .eq(StringUtils.equals("Broker",type),SmShipRent::getBroker,key)
        );
        if(rentList.size() == 0){
            return JsonResult.failed("暂无数据");
        }
        Calendar calendar = Calendar.getInstance();
        for (SmShipRent smShipRent : rentList) {
            calendar.setTime(smShipRent.getRentDate());
            int year = calendar.get(Calendar.YEAR);
            String week = smShipRent.getRentWeek() < 10 ? "0"+smShipRent.getRentWeek() : String.valueOf(smShipRent.getRentWeek());
            smShipRent.setFormatDate(year+"-"+week);
        }
        String url = "";
        if(StringUtils.equals("Broker",type)){
            url = this.summaryBrokerExport(rentList,key);
        }else if(StringUtils.equals("Area",type)){
            url = this.summaryAreaExport(rentList,key);
        }else{
            url = this.summaryTermExport(rentList,key);
        }
        return JsonResult.success(url);
    }

    @Override
    public JsonResult summaryMenuTree() {
        List<JSONObject> resultList = new LinkedList<>();
        //spot
        JSONObject spotJson = new JSONObject();
        spotJson.put("title","i.Spot");
        spotJson.put("key","Spot");
        JSONArray spotArray = new JSONArray();
        //broker
        JSONObject brokerJson = new JSONObject();
        brokerJson.put("title","1.Broker");
        brokerJson.put("key","Broker");
        JSONArray brokerArray = new JSONArray();
        List<String> brokerList = this.baseMapper.getBrokerList();
        for (int i = 0; i < brokerList.size(); i++) {
            JSONObject brokerDetailJson = new JSONObject();
            char c = (char) ('a' + i);
            String brokerName = brokerList.get(i);
            brokerDetailJson.put("title",c+"."+brokerName);
            brokerDetailJson.put("key",brokerName);
            brokerDetailJson.put("isLeaf",true);
            brokerArray.add(brokerDetailJson);
        }
        brokerJson.put("children",brokerArray);
        spotArray.add(brokerJson);
        //area
        JSONObject areaJson = new JSONObject();
        areaJson.put("title","2.Area");
        areaJson.put("key","Area");
        JSONArray areaArray = new JSONArray();
        JSONObject westJson = new JSONObject();
        westJson.put("title","a.West");
        westJson.put("key","West");
        westJson.put("isLeaf",true);
        areaArray.add(westJson);
        JSONObject eastJson = new JSONObject();
        eastJson.put("title","b.East");
        eastJson.put("key","East");
        eastJson.put("isLeaf",true);
        areaArray.add(eastJson);
        areaJson.put("children",areaArray);
        spotArray.add(areaJson);

        spotJson.put("children",spotArray);
        resultList.add(spotJson);

        //Term
        JSONObject termJson = new JSONObject();
        termJson.put("title","ii.Term");
        termJson.put("key","Term");
        JSONArray termArray = new JSONArray();
        List<String> termList = new ArrayList<>();
        termList.add("");
        termList.add("6 Months");
        termList.add("12 Months");
        termList.add("36 Months");
        termList.add("5 Years");
        termList.add("10 Years");
        for (int i = 1; i < 6; i++) {
            JSONObject termDetailJson = new JSONObject();
            termDetailJson.put("title",i+"."+termList.get(i));
            termDetailJson.put("key",termList.get(i));
            termDetailJson.put("isLeaf",true);
            termArray.add(termDetailJson);
        }
        termJson.put("children",termArray);
        resultList.add(termJson);
        return JsonResult.success(resultList);
    }

    @Override
    public JsonResult<List<SmShipRentAnalysisVo>> analysisList(SmShipRentAnalysisBo analysisBo) {
        if(analysisBo.getStartDate() == null || analysisBo.getEndDate() == null){
            return JsonResult.failed("时间范围不能为空！");
        }
        //当前周
        List<SmShipRent> currentList = this.baseMapper.selectList(
                new LambdaQueryWrapper<SmShipRent>()
                .ge(SmShipRent::getRentDate,analysisBo.getStartDate())
                .le(SmShipRent::getRentDate,analysisBo.getEndDate())
        );
        SmShipRentAnalysisFormatVo currentFormat = formatRentListAnalysis(currentList, new BigDecimal("1"));


        //上一周
        List<SmShipRent> lastList = this.baseMapper.selectList(
                new LambdaQueryWrapper<SmShipRent>()
                        .ge(SmShipRent::getRentDate, cn.hutool.core.date.DateUtil.offsetDay(analysisBo.getStartDate(),-7))
                        .le(SmShipRent::getRentDate,cn.hutool.core.date.DateUtil.offsetDay(analysisBo.getEndDate(),-7))
        );
        SmShipRentAnalysisFormatVo lastFormat = formatRentListAnalysis(lastList, new BigDecimal("1"));

        //组装最终数据
        List<SmShipRentAnalysisVo> resultList = new LinkedList<>();
        resultList.addAll(getFormatResetList("现货船日租金(美元)","东线",currentFormat.getEast138(),currentFormat.getEast155()
                ,currentFormat.getEast174(),lastFormat.getEast138(),lastFormat.getEast155(),lastFormat.getEast174()));
        resultList.addAll(getFormatResetList("现货船日租金(美元)","西线",currentFormat.getWest138(),currentFormat.getWest155()
                ,currentFormat.getWest174(),lastFormat.getWest138(),lastFormat.getWest155(),lastFormat.getWest174()));
        resultList.addAll(getFormatResetList("期租船日租金(美元)","6 个月",currentFormat.getSix138(),currentFormat.getSix155()
                ,currentFormat.getSix174(),lastFormat.getSix138(),lastFormat.getSix155(),lastFormat.getSix174()));
        resultList.addAll(getFormatResetList("期租船日租金(美元)","1 年",currentFormat.getTwelve138(),currentFormat.getTwelve155()
                ,currentFormat.getTwelve174(),lastFormat.getTwelve138(),lastFormat.getTwelve155(),lastFormat.getTwelve174()));
        resultList.addAll(getFormatResetList("期租船日租金(美元)","3 年",currentFormat.getThirtySix138(),currentFormat.getThirtySix155()
                ,currentFormat.getThirtySix174(),lastFormat.getThirtySix138(),lastFormat.getThirtySix155(),lastFormat.getThirtySix174()));
        return JsonResult.success(resultList);
    }

    @Override
    public JsonResult analysisStatistics(SmShipRentAnalysisBo analysisBo) {
        if(analysisBo.getStartDate() == null || analysisBo.getEndDate() == null){
            return JsonResult.failed("时间范围不能为空！");
        }
        List<SmShipRent> currentList = this.baseMapper.selectList(
                new LambdaQueryWrapper<SmShipRent>()
                        .ge(SmShipRent::getRentDate,analysisBo.getStartDate())
                        .le(SmShipRent::getRentDate,analysisBo.getEndDate())
        );
        if(currentList.size() == 0){
            return JsonResult.failed("当前时段无数据！");
        }
        Calendar calendar = Calendar.getInstance();

        //判断是否跨年，如果跨年了需要将年份显示出来
        calendar.setTime(analysisBo.getStartDate());
        int startYear = calendar.get(Calendar.YEAR);
        calendar.setTime(analysisBo.getEndDate());
        int endYear = calendar.get(Calendar.YEAR);
        Boolean isDif = (startYear == endYear) ? false : true;

        List<SmShipRentAnalysisFormatVo> resultList = new LinkedList<>();
        for (SmShipRent smShipRent : currentList) {
            calendar.setTime(smShipRent.getRentDate());
            int year = calendar.get(Calendar.YEAR);
            String week = smShipRent.getRentWeek() < 10 ? "0"+smShipRent.getRentWeek() : String.valueOf(smShipRent.getRentWeek());
            smShipRent.setFormatDate(year+"-"+week);
        }
        Map<String,List<SmShipRent>> rentMap = currentList.stream().collect(Collectors.groupingBy(SmShipRent::getFormatDate));
        rentMap = rentMap.entrySet().stream()
                .sorted(Map.Entry.comparingByKey())
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue, (e1, e2) -> e1, LinkedHashMap::new));
        for (String s : rentMap.keySet()) {
            List<SmShipRent> detailList = rentMap.get(s);
            SmShipRentAnalysisFormatVo formatVo = formatRentListAnalysis(detailList, new BigDecimal("1000"));

            calendar.setTime(detailList.get(0).getRentDate());
            int year = calendar.get(Calendar.YEAR);
            String month = String.valueOf(calendar.get(Calendar.MONTH) + 1);
            String day = String.valueOf(calendar.get(Calendar.DAY_OF_MONTH));
            if(month.length() < 2){
                month = ("0"+month);
            }
            if(day.length() < 2){
                day = ("0"+day);
            }
            String key = (month+"-"+day);
            if(isDif){
                key = (year+"-"+key);
            }
            formatVo.setKey(key);
            resultList.add(formatVo);
        }
        return JsonResult.success(resultList);
    }

    @Override
    public JsonResult analysisListExport(SmShipRentAnalysisBo analysisBo) throws IOException {
        JsonResult<List<SmShipRentAnalysisVo>> resultList = this.analysisList(analysisBo);
        if(resultList.getResultCode() != BaseResultCode.OK.getCode()){
            return resultList;
        }
        List<SmShipRentAnalysisVo> dataList = resultList.getResultObject();

        List<String> vesselType = new LinkedList<>();
        vesselType.add("138-150k ST");
        vesselType.add("155-165k TFDE");
        vesselType.add("174k 2-Stroke/MEGI/XDF");

        // 创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        String sheetName = "租金分析";
        // 创建一个工作表
        Sheet sheet = workbook.createSheet(sheetName);
        // 创建一个行
        Row row = sheet.createRow(0);
        // 创建一个单元格并设置值
        Cell cell = row.createCell(0);
        cell.setCellValue("");
        Cell cell1 = row.createCell(1);
        cell1.setCellValue("");
        Cell cell2 = row.createCell(2);
        cell2.setCellValue("138-150k ST");
        Cell cell3 = row.createCell(3);
        cell3.setCellValue("155-165k TFDE");
        Cell cell4 = row.createCell(4);
        cell4.setCellValue("174k 2-Stroke/MEGI/XDF");

        for (int i = 0; i < dataList.size(); i++) {
            SmShipRentAnalysisVo bo = dataList.get(i);
            Row dataRow = sheet.createRow(i+1);
            dataRow.createCell(0).setCellValue(bo.getKey1());
            dataRow.createCell(1).setCellValue(bo.getKey2());
            dataRow.createCell(2).setCellValue(bo.getSpot138());
            dataRow.createCell(3).setCellValue(bo.getSpot155());
            dataRow.createCell(4).setCellValue(bo.getSpot174());
        }

        // 合并单元格
        CellRangeAddress key1Region = new CellRangeAddress(1, 4, 0, 0);
        sheet.addMergedRegion(key1Region);
        setRegionStyle(sheet, key1Region, cellStyle);
        CellRangeAddress key2Region = new CellRangeAddress(5, 10, 0, 0);
        sheet.addMergedRegion(key2Region);
        setRegionStyle(sheet, key2Region, cellStyle);
        int startNum = 1;
        for (int i = 0; i < 5; i++) {
            CellRangeAddress region = new CellRangeAddress(startNum, startNum+1, 1, 1);
            sheet.addMergedRegion(region);
            setRegionStyle(sheet, region, cellStyle);
            startNum = (startNum + 2);
        }

        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j);
                    }
                    cell.setCellStyle(cellStyle);
                }
            }
        }

        //设置后续数据的列宽
        sheet.setColumnWidth(0,10000);
        sheet.setColumnWidth(1,6000);
        for (int i = 2; i < 5; i++) {
            sheet.setColumnWidth(i,6000);
        }

        // 获取当前日期
        LocalDateTime currentDate = LocalDateTime.now();
        // 格式化日期为指定格式（例如：年月日）
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy.MM.dd HH.mm");
        String formattedDate = currentDate.format(formatter);
        String excelName = (sheetName+formattedDate+".xlsx");
        try (FileOutputStream fileOut = new FileOutputStream(excelName)) {
            workbook.write(fileOut);
            fileOut.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //文件地址
        File file = new File(excelName);
//        //声明参数集合
//        HashMap<String, Object> paramMap = new HashMap<>();
//        //文件
//        paramMap.put("file", file);
//        //输出
//        paramMap.put("output","json");
//        //自定义路径
//        paramMap.put("path","image");
//        //场景
//        paramMap.put("scene","image");
//
//        paramMap.put("filename",excelName);
        //上传
//        String result= HttpUtil.post(fileServerHost+"/group1/upload", paramMap);
//        System.out.println("数据导出文件上传结果日志打印："+result);

        // 使用Gson解析JSON
//        Gson gson = new Gson();
//        JsonObject jsonObject = gson.fromJson(result, JsonObject.class);
        // 获取"url"字段的值
//        String url = jsonObject.get("url").getAsString();
        FileInputStream input = new FileInputStream(file);
        MultipartFile multipartFile = new MockMultipartFile("file", file.getName(), "application/octet-stream", input);
        R<SysFile> result1 = remoteFileService.upload(multipartFile);
        File excelFile = new File(excelName);
        if (excelFile.exists()) {
            if (excelFile.delete()) {
            }
        }
        if(result1.getCode() != R.SUCCESS){
            throw new BusException("文件服务器连接失败", BaseResultCode.GENERAL_ERROR);
        }
        String url = result1.getData().getHttpsUrl();
        return JsonResult.success(url);
    }

    /**
     * 最终数据组装
     * @param key1
     * @param key2
     * @param currentSpot138
     * @param currentSpot155
     * @param currentSpot174
     * @param lastSpot138
     * @param lastSpot155
     * @param lastSpot174
     * @return
     */
    public List<SmShipRentAnalysisVo> getFormatResetList(String key1,String key2,BigDecimal currentSpot138,BigDecimal currentSpot155,
           BigDecimal currentSpot174,BigDecimal lastSpot138,BigDecimal lastSpot155,BigDecimal lastSpot174){
        List<SmShipRentAnalysisVo> resultList = new LinkedList<>();
        SmShipRentAnalysisVo westVo1 = new SmShipRentAnalysisVo();
        westVo1.setKey1(key1);
        westVo1.setKey2(key2);
        westVo1.setSpot138(NumberFormatUtil.numericFormatting(currentSpot138));
        westVo1.setSpot155(NumberFormatUtil.numericFormatting(currentSpot155));
        westVo1.setSpot174(NumberFormatUtil.numericFormatting(currentSpot174));
        resultList.add(westVo1);
        SmShipRentAnalysisVo westVo2 = new SmShipRentAnalysisVo();
        westVo2.setKey1(key1);
        westVo2.setKey2(key2);
        BigDecimal westRate138 = (currentSpot138.subtract(lastSpot138)).setScale(0, RoundingMode.HALF_UP);
        BigDecimal westRate155 = (currentSpot155.subtract(lastSpot155)).setScale(0, RoundingMode.HALF_UP);
        BigDecimal westRate174 = (currentSpot174.subtract(lastSpot174)).setScale(0, RoundingMode.HALF_UP);
        if(lastSpot138.compareTo(BigDecimal.ZERO) != 0){
            westVo2.setSpot138(westRate138+","+westRate138.multiply(new BigDecimal("100")).divide(lastSpot138,2,RoundingMode.HALF_UP)+"%");
        }else{
            if(westRate138.compareTo(BigDecimal.ZERO) == 0){
                westVo2.setSpot138(westRate138+","+"0.00%");
            }else{
                westVo2.setSpot138(westRate138+","+"100%");
            }
        }
        if(lastSpot155.compareTo(BigDecimal.ZERO) !=  0){

            westVo2.setSpot155(westRate155+","+westRate155.multiply(new BigDecimal("100")).divide(lastSpot155,2,RoundingMode.HALF_UP)+"%");
        }else{
            if(westRate138.compareTo(BigDecimal.ZERO) == 0){
                westVo2.setSpot155(westRate155+","+"0.00%");
            }else{
                westVo2.setSpot155(westRate155+","+"100%");
            }
        }
        if(lastSpot174.compareTo(BigDecimal.ZERO) !=  0){
            westVo2.setSpot174(westRate174+","+westRate174.multiply(new BigDecimal("100")).divide(lastSpot174,2,RoundingMode.HALF_UP)+"%");
        }else{
            if(westRate138.compareTo(BigDecimal.ZERO) == 0){
                westVo2.setSpot174(westRate174+","+"0.00%");
            }else{
                westVo2.setSpot174(westRate174+","+"100%");
            }
        }
        resultList.add(westVo2);
        return resultList;
    }

    /**
     * 设置单元格样式
     * @param sheet
     * @param region
     * @param cs
     */
    public static void setRegionStyle(Sheet sheet, CellRangeAddress region, CellStyle cs) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            Row row = sheet.getRow(i);
            if (null == row) row = sheet.createRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                Cell cell = row.getCell(j);
                if (null == cell) cell = row.createCell(j);
                cell.setCellStyle(cs);
            }
        }
    }

    /**
     * 列表分析数据组装
     * @param rentList
     * @return
     */
    public SmShipRentAnalysisFormatVo formatRentListAnalysis(List<SmShipRent> rentList, BigDecimal rate){
        SmShipRentAnalysisFormatVo resultData = new SmShipRentAnalysisFormatVo();
        BigDecimal alleast138 = BigDecimal.ZERO;
        BigDecimal alleast155 = BigDecimal.ZERO;
        BigDecimal alleast174 = BigDecimal.ZERO;
        BigDecimal allwest138 = BigDecimal.ZERO;
        BigDecimal allwest155 = BigDecimal.ZERO;
        BigDecimal allwest174 = BigDecimal.ZERO;
        BigDecimal allsix138 = BigDecimal.ZERO;
        BigDecimal allsix155 = BigDecimal.ZERO;
        BigDecimal allsix174 = BigDecimal.ZERO;
        BigDecimal alltwelve138 = BigDecimal.ZERO;
        BigDecimal alltwelve155 = BigDecimal.ZERO;
        BigDecimal alltwelve174 = BigDecimal.ZERO;
        BigDecimal allthirtySix138 = BigDecimal.ZERO;
        BigDecimal allthirtySix155 = BigDecimal.ZERO;
        BigDecimal allthirtySix174 = BigDecimal.ZERO;

        int broeast138 = 0;
        int broeast155 = 0;
        int broeast174 = 0;
        int browest138 = 0;
        int browest155 = 0;
        int browest174 = 0;
        int brosix138 = 0;
        int brosix155 = 0;
        int brosix174 = 0;
        int brotwelve138 = 0;
        int brotwelve155 = 0;
        int brotwelve174 = 0;
        int brothirtySix138 = 0;
        int brothirtySix155 = 0;
        int brothirtySix174 = 0;

        Map<String,List<SmShipRent>> brokerMap = rentList.stream().collect(Collectors.groupingBy(SmShipRent::getBroker));
        for (String s : brokerMap.keySet()) {
            List<SmShipRent> dataList = brokerMap.get(s);
            BigDecimal east138 = BigDecimal.ZERO;
            BigDecimal east155 = BigDecimal.ZERO;
            BigDecimal east174 = BigDecimal.ZERO;
            BigDecimal west138 = BigDecimal.ZERO;
            BigDecimal west155 = BigDecimal.ZERO;
            BigDecimal west174 = BigDecimal.ZERO;
            BigDecimal six138 = BigDecimal.ZERO;
            BigDecimal six155 = BigDecimal.ZERO;
            BigDecimal six174 = BigDecimal.ZERO;
            BigDecimal twelve138 = BigDecimal.ZERO;
            BigDecimal twelve155 = BigDecimal.ZERO;
            BigDecimal twelve174 = BigDecimal.ZERO;
            BigDecimal thirtySix138 = BigDecimal.ZERO;
            BigDecimal thirtySix155 = BigDecimal.ZERO;
            BigDecimal thirtySix174 = BigDecimal.ZERO;
            for (SmShipRent smShipRent : dataList) {
                if(smShipRent.getVesselType().intValue() == 1){
                    if(smShipRent.getSpotEast() != null){
                        east138 = east138.add(smShipRent.getSpotEast());
                    }
                    if(smShipRent.getSpotWest() != null){
                        west138 = west138.add(smShipRent.getSpotWest());
                    }
                    if(smShipRent.getRentSixMonth() != null){
                        six138 = six138.add(smShipRent.getRentSixMonth());
                    }
                    if(smShipRent.getRentTwelveMonth() != null){
                        twelve138 = twelve138.add(smShipRent.getRentTwelveMonth());
                    }
                    if(smShipRent.getRentThirtySixMonth() != null){
                        thirtySix138 = thirtySix138.add(smShipRent.getRentThirtySixMonth());
                    }
                }else if(smShipRent.getVesselType().intValue() == 2){
                    if(smShipRent.getSpotEast() != null){
                        east155 = east155.add(smShipRent.getSpotEast());
                    }
                    if(smShipRent.getSpotWest() != null){
                        west155 = west155.add(smShipRent.getSpotWest());
                    }
                    if(smShipRent.getRentSixMonth() != null){
                        six155 = six155.add(smShipRent.getRentSixMonth());
                    }
                    if(smShipRent.getRentTwelveMonth() != null){
                        twelve155 = twelve155.add(smShipRent.getRentTwelveMonth());
                    }
                    if(smShipRent.getRentThirtySixMonth() != null){
                        thirtySix155 = thirtySix155.add(smShipRent.getRentThirtySixMonth());
                    }
                }else{
                    if(smShipRent.getSpotEast() != null){
                        east174 = east174.add(smShipRent.getSpotEast());
                    }
                    if(smShipRent.getSpotWest() != null){
                        west174 = west174.add(smShipRent.getSpotWest());
                    }
                    if(smShipRent.getRentSixMonth() != null){
                        six174 = six174.add(smShipRent.getRentSixMonth());
                    }
                    if(smShipRent.getRentTwelveMonth() != null){
                        twelve174 = twelve174.add(smShipRent.getRentTwelveMonth());
                    }
                    if(smShipRent.getRentThirtySixMonth() != null){
                        thirtySix174 = thirtySix174.add(smShipRent.getRentThirtySixMonth());
                    }
                }
            }
            if(east138.compareTo(BigDecimal.ZERO) > 0){
                alleast138 = alleast138.add(east138);
                broeast138 ++;
            }
            if(east155.compareTo(BigDecimal.ZERO) > 0){
                alleast155 = alleast155.add(east155);
                broeast155 ++;
            }
            if(east174.compareTo(BigDecimal.ZERO) > 0){
                alleast174 = alleast174.add(east174);
                broeast174 ++;
            }
            if(west138.compareTo(BigDecimal.ZERO) > 0){
                allwest138 = allwest138.add(west138);
                browest138 ++;
            }
            if(west155.compareTo(BigDecimal.ZERO) > 0){
                allwest155 = allwest155.add(west155);
                browest155 ++;
            }
            if(west174.compareTo(BigDecimal.ZERO) > 0){
                allwest174 = allwest174.add(west174);
                browest174 ++;
            }
            if(six138.compareTo(BigDecimal.ZERO) > 0){
                allsix138 = allsix138.add(six138);
                brosix138 ++;
            }
            if(six155.compareTo(BigDecimal.ZERO) > 0){
                allsix155 = allsix155.add(six155);
                brosix155 ++;
            }
            if(six174.compareTo(BigDecimal.ZERO) > 0){
                allsix174 = allsix174.add(six174);
                brosix174 ++;
            }
            if(twelve138.compareTo(BigDecimal.ZERO) > 0){
                alltwelve138 = alltwelve138.add(twelve138);
                brotwelve138 ++;
            }
            if(twelve155.compareTo(BigDecimal.ZERO) > 0){
                alltwelve155 = alltwelve155.add(twelve155);
                brotwelve155 ++;
            }
            if(twelve174.compareTo(BigDecimal.ZERO) > 0){
                alltwelve174 = alltwelve174.add(twelve174);
                brotwelve174 ++;
            }
            if(thirtySix138.compareTo(BigDecimal.ZERO) > 0){
                allthirtySix138 = allthirtySix138.add(thirtySix138);
                brothirtySix138 ++;
            }
            if(thirtySix155.compareTo(BigDecimal.ZERO) > 0){
                allthirtySix155 = allthirtySix155.add(thirtySix155);
                brothirtySix155 ++;
            }
            if(thirtySix174.compareTo(BigDecimal.ZERO) > 0){
                allthirtySix174 = allthirtySix174.add(thirtySix174);
                brothirtySix174 ++;
            }

        }

        resultData.setEast138(broeast138 == 0 ? BigDecimal.ZERO : alleast138.divide(rate).divide(new BigDecimal(String.valueOf(broeast138)),3,RoundingMode.HALF_UP));
        resultData.setEast155(broeast155 == 0 ? BigDecimal.ZERO : alleast155.divide(rate).divide(new BigDecimal(String.valueOf(broeast155)),3,RoundingMode.HALF_UP));
        resultData.setEast174(broeast174 == 0 ? BigDecimal.ZERO : alleast174.divide(rate).divide(new BigDecimal(String.valueOf(broeast174)),3,RoundingMode.HALF_UP));
        resultData.setWest138(browest138 == 0 ? BigDecimal.ZERO : allwest138.divide(rate).divide(new BigDecimal(String.valueOf(browest138)),3,RoundingMode.HALF_UP));
        resultData.setWest155(browest155 == 0 ? BigDecimal.ZERO : allwest155.divide(rate).divide(new BigDecimal(String.valueOf(browest155)),3,RoundingMode.HALF_UP));
        resultData.setWest174(browest174 == 0 ? BigDecimal.ZERO : allwest174.divide(rate).divide(new BigDecimal(String.valueOf(browest174)),3,RoundingMode.HALF_UP));
        resultData.setSix138(brosix138 == 0 ? BigDecimal.ZERO : allsix138.divide(rate).divide(new BigDecimal(String.valueOf(brosix138)),3,RoundingMode.HALF_UP));
        resultData.setSix155(brosix155 == 0 ? BigDecimal.ZERO : allsix155.divide(rate).divide(new BigDecimal(String.valueOf(brosix155)),3,RoundingMode.HALF_UP));
        resultData.setSix174(brosix174 == 0 ? BigDecimal.ZERO : allsix174.divide(rate).divide(new BigDecimal(String.valueOf(brosix174)),3,RoundingMode.HALF_UP));
        resultData.setTwelve138(brotwelve138 == 0 ? BigDecimal.ZERO : alltwelve138.divide(rate).divide(new BigDecimal(String.valueOf(brotwelve138)),3,RoundingMode.HALF_UP));
        resultData.setTwelve155(brotwelve155 == 0 ? BigDecimal.ZERO : alltwelve155.divide(rate).divide(new BigDecimal(String.valueOf(brotwelve155)),3,RoundingMode.HALF_UP));
        resultData.setTwelve174(brotwelve174 == 0 ? BigDecimal.ZERO : alltwelve174.divide(rate).divide(new BigDecimal(String.valueOf(brotwelve174)),3,RoundingMode.HALF_UP));
        resultData.setThirtySix138(brothirtySix138 == 0 ? BigDecimal.ZERO : allthirtySix138.divide(rate).divide(new BigDecimal(String.valueOf(brothirtySix138)),3,RoundingMode.HALF_UP));
        resultData.setThirtySix155(brothirtySix155 == 0 ? BigDecimal.ZERO : allthirtySix155.divide(rate).divide(new BigDecimal(String.valueOf(brothirtySix155)),3,RoundingMode.HALF_UP));
        resultData.setThirtySix174(brothirtySix174 == 0 ? BigDecimal.ZERO : allthirtySix174.divide(rate).divide(new BigDecimal(String.valueOf(brothirtySix174)),3,RoundingMode.HALF_UP));
        return resultData;
    }

}
